/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ipartek.formacion.gestformwww.modelo.clases;

import com.ipartek.formacion.gestformwww.modelo.beans.FiltroMaterial;
import com.ipartek.formacion.gestformwww.modelo.beans.Material;
import com.ipartek.formacion.gestformwww.modelo.beans.excepciones.GestorFormacionExcepcion;
import com.ipartek.formacion.gestformwww.modelo.interfaces.IMaterialDAO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.ResourceBundle;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Ekaitz,santi
 * La clase material, hace referencia a la tabla libro de la BD
 */
public class SQLServerMaterialDAO implements IMaterialDAO {

    private static ResourceBundle rb;

    public SQLServerMaterialDAO() {
        rb = ResourceBundle.getBundle("com.ipartek.formacion.gestformwww.controlador.excepciones");
    }

    public int insertarMaterial(Material material) throws GestorFormacionExcepcion {
        int idMaterial = 0;
        Statement stmt = null;
        Connection con = null;
         ResultSet rs = null;
        String sql = "INSERT INTO LIBRO (ISBN,LibAutor,LibEditorial,LibAño,LibPag,LibPVP,LibTitulo,LibObserv,PedId,LibCantidadDoc,LibCantidadAlu) VALUES ('" + material.getIsbn() + "','" + material.getLibAutor() + "','" + material.getLibEditorial() + "','" + material.getLibAnyo() + "','" + material.getLibPag() + "','" + material.getLibPVP() + "','" + material.getLibTitulo() + "','" + material.getLibObserv() + "','" + material.getPedId() + "','" + material.getLibCantidadDoc() + "','" + material.getLibCantidadAlu() + "')";
        try {
            con = SQLServerDAOFactory.conecta();
        } catch (SQLException ex) {
             Logger.getLogger(SQLServerMaterialDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-conexion"));
        }
        try {
            stmt = con.createStatement();
            stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
            rs = stmt.getGeneratedKeys();
                if (rs.next()) {
                    idMaterial = rs.getInt(1);
                }
        } catch (SQLException ex) {
            Logger.getLogger(SQLServerMaterialDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-sql"));
        }

        closeAll(con, stmt);
        return idMaterial;
    }

    public void borrarMaterial(Material material)throws GestorFormacionExcepcion {

        Statement stmt = null;
        Connection con = null;
        try {
            con = SQLServerDAOFactory.conecta();
        } catch (SQLException ex) {
             Logger.getLogger(SQLServerMaterialDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-conexion"));
        }
        try {
            String sql = "DELETE FROM Prestamo WHERE LibId = " + material.getLibId();

            stmt = con.createStatement();
            stmt.executeUpdate(sql);

            sql = "DELETE FROM LIBRO WHERE LibId = " + material.getLibId();
           
            stmt = con.createStatement();
            stmt.executeUpdate(sql);
        } catch (SQLException ex) {
            Logger.getLogger(SQLServerMaterialDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-sql"));
        }
        closeAll(con, stmt);
    }

    public void modificarMaterial(Material material)throws GestorFormacionExcepcion {

        Statement stmt = null;
        Connection con = null;
        try {
            con = SQLServerDAOFactory.conecta();
        } catch (SQLException ex) {
             Logger.getLogger(SQLServerMaterialDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-conexion"));
        }
        try {
            String sql = "UPDATE LIBRO SET "
                    + "ISBN='" + material.getIsbn() + "', "
                    + "LibAutor='" + material.getLibAutor() + "', "
                    + "LibEditorial='" + material.getLibEditorial() + "', "
                    + "LibAño='" + material.getLibAnyo() + "', "
                    + "LibPag='" + material.getLibPag() + "', "
                    + "LibPVP='" + material.getLibPVP() + "', "
                    + "LibTitulo='" + material.getLibTitulo() + "', "
                    + "LibObserv='" + material.getLibObserv() + "', "
                    + "PedId='" + material.getPedId() + "', "
                    + "LibCantidadDoc='" + material.getLibCantidadDoc()+ "', "
                    + "LibCantidadAlu='" + material.getLibCantidadAlu() + "' "
                    + "WHERE LibId=" + material.getLibId();
                    

            stmt = con.createStatement();
            stmt.executeUpdate(sql);
        } catch (SQLException ex) {
           Logger.getLogger(SQLServerMaterialDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-sql"));
        }
        closeAll(con, stmt);
    }

    public void modificarStockMaterial(int idMaterial, int Cantidad)throws GestorFormacionExcepcion {

        Statement stmt = null;
        Connection con = null;
        try {
            con = SQLServerDAOFactory.conecta();
        } catch (SQLException ex) {
             Logger.getLogger(SQLServerMaterialDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-conexion"));
        }
        try {
            String sql = "UPDATE LIBRO SET "
                    + "LibCantidadAlu = LibCantidadAlu + " + Cantidad
                    + "WHERE LibId=" + idMaterial;


            stmt = con.createStatement();
            stmt.executeUpdate(sql);
        } catch (SQLException ex) {
           Logger.getLogger(SQLServerMaterialDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-sql"));
        }
        closeAll(con, stmt);
    }

    public ArrayList<Material> listarMateriales(FiltroMaterial filtro)throws GestorFormacionExcepcion {

        ArrayList<Material> listaMateriales = new ArrayList<Material>();
        //Statement stmt = null;
       // ResultSet rs = null;
        String sql= null;
        Connection con = null;
        try {
            con = SQLServerDAOFactory.conecta();
        } catch (SQLException ex) {
             Logger.getLogger(SQLServerMaterialDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-conexion"));
        }
        try {
            
            sql = filtro.getSQL();
            PreparedStatement stmt = con.prepareStatement(sql,
                                                    ResultSet.TYPE_SCROLL_SENSITIVE,
                                                    ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = stmt.executeQuery();
            if (filtro.getPrimerElemento()>0)
                {
                    rs.absolute(filtro.getPrimerElemento()-1);
                }
            for( int i=filtro.getPrimerElemento()-1;i<filtro.getUltimoElemento();i++)
            {

                if (rs.next())
                {
                    if((rs.getRow()>=filtro.getPrimerElemento()&& rs.getRow()<=filtro.getUltimoElemento())){
                        Material material = new Material();
                        material.setLibId(rs.getInt("LibId"));
                        material.setIsbn(rs.getString("ISBN"));
                        material.setLibAnyo(rs.getInt("LibAño"));
                        material.setLibPag(rs.getInt("LibPag"));
                        material.setLibPVP(rs.getFloat("LibPVP"));
                        material.setLibObserv(rs.getString("LibObserv"));
                        material.setLibCantidadDoc(rs.getInt("LibCantidadDoc"));
                        material.setLibCantidadAlu(rs.getInt("LibCantidadAlu"));
                        material.setLibAutor(rs.getString("LibAutor"));
                        material.setLibEditorial(rs.getString("LibEditorial"));
                        material.setLibTitulo(rs.getString("LibTitulo"));
                        material.setPedId(rs.getString("PedId"));
                        listaMateriales.add(material);
                    }
                }

            }
        stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(SQLServerMaterialDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-sql"));
        }
       return listaMateriales;
    }

    /**
     * Metodo que cuenta el número de materiales devueltos al aplicar el filtro establecido.
     * @param filtro: Contiene los datos de filtrado del candidato.
     * @return Devuelve el número de registros obtenidos.
     */
    public int contarMaterial(FiltroMaterial filtro) throws GestorFormacionExcepcion {
        int registros = 0;
        Connection conexion = null;
        String sql = filtro.getSQL();

        try {
            conexion = SQLServerDAOFactory.conecta();
        } catch (SQLException ex) {
             Logger.getLogger(SQLServerMaterialDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-conexion"));
        }

        try {
            PreparedStatement stmt = conexion.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = stmt.executeQuery();

            if (rs.last()) {
                registros = rs.getRow();
            }
            stmt.close();
        } catch (SQLException ex) {
             Logger.getLogger(SQLServerMaterialDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-sql"));
        } finally {
            SQLServerDAOFactory.desconecta(conexion);
        }

        return registros;
    }

    public void closeAll(Connection con, Statement stmt) {
        try {
            stmt.close();
            SQLServerDAOFactory.desconecta(con);
        } catch (SQLException ex) {
            Logger.getLogger(SQLServerMaterialDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void closeAll(Connection con, Statement stmt, ResultSet rs) {
        try {
            stmt.close();
            SQLServerDAOFactory.desconecta(con);
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(SQLServerMaterialDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public Material getMaterial(int idMaterial)throws GestorFormacionExcepcion {
        PreparedStatement stmt = null;
        Connection con = null;
        Material material = null;
        String sql = "";
        try {
            con = SQLServerDAOFactory.conecta();
        } catch (SQLException ex) {
             Logger.getLogger(SQLServerMaterialDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-conexion"));
        }

        try {
            sql = "SELECT LibId, ISBN, LibAutor, LibEditorial, LibAño, LibPag, LibPVP, LibTitulo, "
                    + "LibObserv, PedId, LibCantidadDoc, LibCantidadAlu "
                    + "FROM Libro "
                    + "WHERE LibId=?";
            stmt = con.prepareStatement(sql);
            stmt.setInt(1, idMaterial);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                material = new Material();
                material.setLibId(rs.getInt("LibId"));
                material.setIsbn(rs.getString("ISBN"));
                material.setLibAnyo(rs.getInt("LibAño"));
                material.setLibPag(rs.getInt("LibPag"));
                material.setLibPVP(rs.getFloat("LibPVP"));
                material.setLibAutor(rs.getString("LibAutor"));
                material.setLibEditorial(rs.getString("LibEditorial"));
                material.setLibTitulo(rs.getString("LibTitulo"));
                material.setLibObserv(rs.getString("LibObserv"));
                material.setPedId(rs.getString("PedId"));
                material.setLibCantidadDoc(rs.getInt("LibCantidadDoc"));
                material.setLibCantidadAlu(rs.getInt("LibCantidadAlu"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(SQLServerCandConvDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-sql"));
        } finally {
            SQLServerDAOFactory.desconecta(con);
            return material;
        }
    }
}
